﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.Configuration;
using System.ComponentModel.DataAnnotations;
using TracNghiem.Models;
using System.Collections.ObjectModel;
using System.Web.Mvc;
using System.Data.OracleClient;

namespace TracNghiem.Areas.Admin.Models
{
    public class TestModel
    {
        public string id { get; set; }
        [Required(ErrorMessage = "Tên chủ đề không được để trống!")]
        public string  name{ get; set; }  
        public string  ghi_chu{ get; set; }
        public string is_pattern { get; set; }
        public string is_test { get; set; }
        public string status { get; set; }
        public string is_test_status { get; set; }
        public string ngay_tao { get; set; }
        public string ngay_ket_thuc { get; set; }
        [Required(ErrorMessage = "Thời gian không được để trống!")]
        public string thoi_gian { get; set; }
        public string dichvu_id { get; set; }
        public string ten_dich_vu { get; set; }
        public List<TestModel> getTestByDV_ID(int dichvu_id)
        {
            List<TestModel> Test = new List<TestModel>();
            string sql="";
            if(dichvu_id>=0)
                sql = "SELECT a.*,b.name ten_dich_vu FROM de_thi a,dichvu b where a.dichvu_id=b.id and a.dichvu_id=" + dichvu_id + " order by a.id desc,a.ngay_ket_thuc desc,a.name";
            else
                sql = "SELECT a.*,'' ten_dich_vu FROM de_thi a where a.dichvu_id is null order by a.id desc,a.ngay_ket_thuc desc,a.name";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            int ngay_taoIndex=0;
            int ngay_ket_thucIndex=0;
            while (odr.Read())
            {
                ngay_taoIndex = odr.GetOrdinal("ngay_tao");
                ngay_ket_thucIndex = odr.GetOrdinal("ngay_ket_thuc");
                Test.Add(new TestModel
                {
                    id = odr["id"].ToString(),
                    name = odr["name"].ToString(),
                    ghi_chu = odr["ghi_chu"].ToString(),
                    is_pattern = odr["is_pattern"].ToString(),
                    status = odr["status"].ToString()=="1"?"Bình thường":"Khóa",
                    ngay_tao = odr.IsDBNull(ngay_taoIndex) ? null : odr.GetDateTime(ngay_taoIndex).ToString("dd/MM/yyyy"),
                    ngay_ket_thuc = odr.IsDBNull(ngay_ket_thucIndex) ? null : odr.GetDateTime(ngay_ket_thucIndex).ToString("dd/MM/yyyy"),
                    thoi_gian = odr["thoi_gian"].ToString(),
                    dichvu_id = odr["dichvu_id"].ToString(),
                    ten_dich_vu = odr["ten_dich_vu"].ToString()
                });
            }
            General.conn_global.Close();
            return Test;
        }
        public List<TestModel> getTestWithNoDV_ID()
        {
            List<TestModel> Test = new List<TestModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT a.* FROM de_thi a where  a.dichvu_id is null order by a.id desc,a.ngay_ket_thuc desc,a.name");
            int ngay_taoIndex = 0;
            int ngay_ket_thucIndex = 0;
            while (odr.Read())
            {
                ngay_taoIndex = odr.GetOrdinal("ngay_tao");
                ngay_ket_thucIndex = odr.GetOrdinal("ngay_ket_thuc");
                Test.Add(new TestModel
                {
                    id = odr["id"].ToString(),
                    name = odr["name"].ToString(),
                    ghi_chu = odr["ghi_chu"].ToString(),
                    is_pattern = odr["is_pattern"].ToString(),
                    status = odr["status"].ToString() == "1" ? "Bình thường" : "Khóa",
                    ngay_tao = odr.IsDBNull(ngay_taoIndex) ? null : odr.GetDateTime(ngay_taoIndex).ToString("dd/MM/yyyy"),
                    ngay_ket_thuc = odr.IsDBNull(ngay_ket_thucIndex) ? null : odr.GetDateTime(ngay_ket_thucIndex).ToString("dd/MM/yyyy"),
                    thoi_gian = odr["thoi_gian"].ToString(),
                    dichvu_id = odr["dichvu_id"].ToString(),
                    is_test = odr["is_test"].ToString(),
                });
            }
            General.conn_global.Close();
            return Test;
        }
    
        public List<TestModel> getTestById(int id)
        {
            List<TestModel> Test = new List<TestModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT a.*,b.dichvu_id,b.name ten_phan_loai FROM de_thi a,phanloai b where a.phanloai_id=b.id and a.id=" + id + " order by b.id,a.ngay_ket_thuc desc,a.name");
            int ngay_taoIndex = 0;
            int ngay_ket_thucIndex = 0;
            while (odr.Read())
            {
                ngay_taoIndex = odr.GetOrdinal("ngay_tao");
                ngay_ket_thucIndex = odr.GetOrdinal("ngay_ket_thuc");
                Test.Add(new TestModel
                {
                    id = odr["id"].ToString(),
                    name = odr["name"].ToString(),
                    ghi_chu = odr["ghi_chu"].ToString(),
                    is_pattern = odr["is_pattern"].ToString(),
                    status = odr["status"].ToString() == "1" ? "Bình thường" : "Khóa",
                    ngay_tao = odr.IsDBNull(ngay_taoIndex) ? null : odr.GetDateTime(ngay_taoIndex).ToString("dd/MM/yyyy"),
                    ngay_ket_thuc = odr.IsDBNull(ngay_ket_thucIndex) ? null : odr.GetDateTime(ngay_ket_thucIndex).ToString("dd/MM/yyyy"),
                    thoi_gian = odr["thoi_gian"].ToString(),
                    dichvu_id = odr["dichvu_id"].ToString(),
                    ten_dich_vu = odr["ten_dich_vu"].ToString()
                });
            }
            General.conn_global.Close();
            return Test;
        }
        public List<SelectListItem> getTestJson(string dichvu_id, string Order = "ID")
        {
            List<SelectListItem> list = new List<SelectListItem>();
            string sql = "";
            if (Convert.ToInt32(dichvu_id) >= -1)
            {
                if(dichvu_id!="-1")
                    sql = "select * from de_thi where dichvu_id=" + Convert.ToInt32(dichvu_id) + " order by " + Order;
                else
                    sql = "select * from de_thi where dichvu_id is null order by " + Order;
                OracleDataReader odr = General.Lib_GetDataReader(sql);
                while (odr.Read())
                {
                    list.Add(new SelectListItem { Text = odr[1].ToString(), Value = odr[0].ToString() });
                }
            }
            General.conn_global.Close();
            return list;
        }
        public List<SelectListItem> getPLJson(string dichvu_id, string Order = "ID")
        {
            List<SelectListItem> list = new List<SelectListItem>();
            if (dichvu_id == "")
                return list;
            if (Convert.ToInt32(dichvu_id) >= 0)
            {
                string sql = "select * from phanloai where dichvu_id=" + Convert.ToInt32(dichvu_id) + " order by " + Order;
                OracleDataReader odr = General.Lib_GetDataReader(sql);
                while (odr.Read())
                {
                    list.Add(new SelectListItem { Text = odr[1].ToString(), Value = odr[0].ToString() });
                }
            }
            General.conn_global.Close();
            return list;
        }
        public void addTest(TestModel ts)
        {
            string ngay_ket_thuc="";
            if(ts.ngay_ket_thuc!=null)
                ngay_ket_thuc = "to_date('" + ts.ngay_ket_thuc+"','dd/mm/yyyy'),";
            else
                ngay_ket_thuc = "to_date('31/1/2024','dd/mm/yyyy'),";
            string sql = "INSERT INTO de_thi";
            sql += " select nvl((select max(id)+1 from de_thi),1),trim('" + ts.name + "')," + ts.status + ","+ts.dichvu_id+","+ts.thoi_gian+",to_date('" + ts.ngay_tao + "','dd/mm/yyyy')," + ngay_ket_thuc + "'" + ts.ghi_chu + "',"+ts.is_pattern+","+ts.is_test+" from dual";
            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public void addTest2(TestModel ts)
        {
            string ngay_ket_thuc = "";
            if (ts.ngay_ket_thuc != null)
                ngay_ket_thuc = "to_date('" + ts.ngay_ket_thuc + "','dd/mm/yyyy'),";
            else
                ngay_ket_thuc = "to_date('31/1/2024','dd/mm/yyyy'),";
            string sql = "INSERT INTO de_thi(id,name,status,thoi_gian,ngay_tao,ngay_ket_thuc,ghi_chu,is_pattern,is_test)";
            sql += " select nvl((select max(id)+1 from de_thi),1),trim('" + ts.name + "')," + ts.status + "," + ts.thoi_gian + ",to_date('" + ts.ngay_tao + "','dd/mm/yyyy')," + ngay_ket_thuc + "'" + ts.ghi_chu + "'," + ts.is_pattern + ","+ts.is_test+" from dual";
            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public void updateTest(TestModel ts)
        {
            string ngay_ket_thuc = "";
            if (ts.ngay_ket_thuc != null)
                ngay_ket_thuc = "ngay_ket_thuc=to_date('" + ts.ngay_ket_thuc + "','dd/mm/yyyy')";
            else
                ngay_ket_thuc = "ngay_ket_thuc=to_date('31/1/2024','dd/mm/yyyy'),";
            string sql = "UPDATE de_thi SET name=trim('" + ts.name + "'), ghi_chu='" + ts.ghi_chu + "', status=" + ts.status + ",is_test=" + ts.is_test_status + ",thoi_gian=" + ts.thoi_gian + "," + ngay_ket_thuc + " where id='" + ts.id + "'";
            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public void updateTestDelete(string id_array, string de_thi_id)
        {
            //string sql = "Delete from de_thi_mau where cau_hoi_id not in (" + id_array + ") and de_thi_id=" + de_thi_id;
            string sql = "update de_thi_mau set cau_hoi_id=null where de_thi_id=" + de_thi_id;
            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public void updateTestDelete2(string id_array,string de_thi_id)
        {
            //string sql = "Delete from de_thi_mau where cau_hoi_id not in (" + id_array + ") and de_thi_id=" + de_thi_id;
            string sql="";
            if(id_array==null)
                sql = "delete from de_thi_ct where de_thi_id=" + de_thi_id;
            else
                sql = "delete from de_thi_ct where id not in (" + id_array + ") and de_thi_id="+de_thi_id;
            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public void deleteTest(int id)
        {
            string sql = "DELETE FROM de_thi WHERE id=" + id + "";
            General.Lib_ExecuteNonQuery(sql);
            string sql1 = "DELETE FROM de_thi_ct WHERE de_thi_id=" + id + "";
            General.Lib_ExecuteNonQuery(sql1);
            string sql2 = "DELETE FROM de_thi_mau WHERE de_thi_id=" + id + "";
            General.Lib_ExecuteNonQuery(sql2);
            General.conn_global.Close();
        }
        public int checkExist(TestModel ts)
        {
            string sql = "SELECT * FROM de_thi WHERE name='" + ts.name + "'";
            return General.Lib_CheckExist(sql);
        }
        public int checkPL_DVExist(string de_thi_id,string phan_loai_id)
        {
            string sql = "SELECT * FROM de_thi_ct WHERE de_thi_id=" + de_thi_id + " and phanloai_id="+phan_loai_id;
            return General.Lib_CheckExist(sql);
        }
        public int checkSubItem(int id)
        {
            string sql = "SELECT ID FROM bai_thi WHERE de_thi_id='" + id + "'";
            return General.Lib_CheckExist(sql);
        }

        public List<TestModel> FindTest(string dichvu_id,string str)
        {
            List<TestModel> Test = new List<TestModel>();
            string sql = "SELECT a.*,b.name ten_dich_vu FROM de_thi a,dichvu b where a.dichvu_id=b.id and a.dichvu_id=" + dichvu_id + " and " + str;
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            int ngay_taoIndex = 0;
            int ngay_ket_thucIndex = 0;
            while (odr.Read())
            {
                ngay_taoIndex = odr.GetOrdinal("ngay_tao");
                ngay_ket_thucIndex = odr.GetOrdinal("ngay_ket_thuc");
                Test.Add(new TestModel
                {
                    id = odr["id"].ToString(),
                    name = odr["name"].ToString(),
                    ghi_chu = odr["ghi_chu"].ToString(),
                    is_pattern = odr["is_pattern"].ToString(),
                    status = odr["status"].ToString() == "1" ? "Bình thường" : "Khóa",
                    ngay_tao = odr.IsDBNull(ngay_taoIndex) ? null : odr.GetDateTime(ngay_taoIndex).ToString("dd/MM/yyyy"),
                    ngay_ket_thuc = odr.IsDBNull(ngay_ket_thucIndex) ? null : odr.GetDateTime(ngay_ket_thucIndex).ToString("dd/MM/yyyy"),
                    thoi_gian = odr["thoi_gian"].ToString(),
                    dichvu_id = odr["dichvu_id"].ToString(),
                    ten_dich_vu = odr["ten_dich_vu"].ToString()
                });
            }
            General.conn_global.Close();
            return Test;
        }
        public List<TestModel> FindTest2(string str)
        {
            List<TestModel> Test = new List<TestModel>();
            string sql = "SELECT a.* FROM de_thi a where  a.dichvu_id is null  " + str + " order by a.id desc,a.ngay_ket_thuc desc,a.name";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            int ngay_taoIndex = 0;
            int ngay_ket_thucIndex = 0;
            while (odr.Read())
            {
                ngay_taoIndex = odr.GetOrdinal("ngay_tao");
                ngay_ket_thucIndex = odr.GetOrdinal("ngay_ket_thuc");
                Test.Add(new TestModel
                {
                    id = odr["id"].ToString(),
                    name = odr["name"].ToString(),
                    ghi_chu = odr["ghi_chu"].ToString(),
                    is_pattern = odr["is_pattern"].ToString(),
                    status = odr["status"].ToString() == "1" ? "Bình thường" : "Khóa",
                    ngay_tao = odr.IsDBNull(ngay_taoIndex) ? null : odr.GetDateTime(ngay_taoIndex).ToString("dd/MM/yyyy"),
                    ngay_ket_thuc = odr.IsDBNull(ngay_ket_thucIndex) ? null : odr.GetDateTime(ngay_ket_thucIndex).ToString("dd/MM/yyyy"),
                    thoi_gian = odr["thoi_gian"].ToString(),
                    dichvu_id = odr["dichvu_id"].ToString(),
                    is_test = odr["is_test"].ToString(),
                });
            }
            General.conn_global.Close();
            return Test;
        } 
    }
    public class Test_DetailModel
    {
        public string id { get; set; }
        public string de_thi_id { get; set; }
        public string phanloai_id { get; set; }
        public string so_cau { get; set; }
        public string dichvu_id { get; set; }
        public string ten_phan_loai { get; set; }

        public string ten_dich_vu { get; set; }
        public List<Test_DetailModel> getTest_Detail(int de_thi_id)
        {
            List<Test_DetailModel> Test = new List<Test_DetailModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT a.*,b.name ten_phan_loai,b.dichvu_id FROM de_thi_ct a,phanloai b where a.phanloai_id=b.id and a.de_thi_id="+de_thi_id +" order by a.id");            
            while (odr.Read())
            {
                Test.Add(new Test_DetailModel
                {
                    id = odr["id"].ToString(),
                    de_thi_id = odr["de_thi_id"].ToString(),
                    phanloai_id = odr["phanloai_id"].ToString(),
                    so_cau = odr["so_cau"].ToString(),
                    dichvu_id = odr["dichvu_id"].ToString(),
                    ten_phan_loai = odr["ten_phan_loai"].ToString()
                });
            }
            General.conn_global.Close();
            return Test;
        }
        public void AddTestDetail(string phanloai_id,string so_cau)
        {
            string sql = "INSERT INTO de_thi_ct";
            sql += " select nvl((select max(id)+1 from de_thi_ct),1),(select max(id) from de_thi)," + phanloai_id + "," + so_cau + " from dual";
            General.conn_global.Close();
            General.Lib_ExecuteNonQuery(sql);
        }
        public void EditTestDetail(string id, string so_cau)
        {
            string sql = "UPDATE de_thi_ct SET so_cau="+ so_cau +" where id="+id;
            General.conn_global.Close();
            General.Lib_ExecuteNonQuery(sql);
        }
    }
    public class Test_PatternModel
    {
        public string id { get; set; }
        public string de_thi_id { get; set; }
        public string cau_hoi_id { get; set; }
        public List<Test_PatternModel> getTest_Pattern(int de_thi_id)
        {
            List<Test_PatternModel> Test = new List<Test_PatternModel>();
            string sql = "SELECT id,de_thi_id,b.cau_hoi_id FROM de_thi_mau,table(cau_hoi_id) b where de_thi_id=" + de_thi_id;
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                Test.Add(new Test_PatternModel
                {
                    id = odr["id"].ToString(),
                    de_thi_id = odr["de_thi_id"].ToString(),
                    cau_hoi_id = odr["cau_hoi_id"].ToString()
                });
            }
            General.conn_global.Close();
            return Test;
        }
        //public void AddTestPattern(string cau_hoi_id)
        //{
        //    string sql = "INSERT INTO de_thi_mau";
        //    sql += " select nvl((select max(id)+1 from de_thi_mau),1),(select max(id) from de_thi)," + cau_hoi_id + " from dual";
        //    General.conn_global.Close();
        //    General.Lib_ExecuteNonQuery(sql);
        //}
        public void AddTestPattern(string[] cau_hoi_id)
        {
            string sql = "INSERT INTO de_thi_mau";
            sql += " select nvl((select max(id)+1 from de_thi_mau),1),(select max(id) from de_thi),CAST(";            
            sql += " multiset(select * from(";
            for (int i = 0; i < cau_hoi_id.Length; i++)
            {
                sql += " select " + cau_hoi_id[i] + " a from dual";
                if (cau_hoi_id.Length != 1 && i < cau_hoi_id.Length - 1)
                    sql += " UNION ";
            }
            sql += " )) as de_thi_mau_table) from dual";

            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public void EditTestPattern(string[] cau_hoi_id,string de_thi_id)
        {
            string sql = "UPDATE de_thi_mau";
            sql += " SET cau_hoi_id = CAST(";
            sql += " multiset(select * from(";
            for (int i = 0; i < cau_hoi_id.Length; i++)
            {
                sql += " select " + cau_hoi_id[i] + " a from dual";
                if (cau_hoi_id.Length != 1 && i < cau_hoi_id.Length - 1)
                    sql += " UNION ";
            }
            sql += " )) as de_thi_mau_table) where de_thi_id="+de_thi_id;

            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public int checkExist(string cau_hoi_id)
        {
            string sql = "SELECT id FROM de_thi_mau a,table(cau_hoi_id) b WHERE b.cau_hoi_id='" + cau_hoi_id + "' and de_thi_id=(select max(id) from de_thi)";
            return General.Lib_CheckExist(sql);
        }
        public int checkExistDethi_Pattern(string cau_hoi_id, string de_thi_id)
        {
            string sql = "SELECT * FROM de_thi_mau WHERE cau_hoi_id='" + cau_hoi_id + "' and de_thi_id=" + de_thi_id;
            return General.Lib_CheckExist(sql);
        }
    }
}